﻿using System;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
namespace Controller
{
    public class DBHelper // This class is written by Mads Rud Nielsen, edit by Trang, Tien
    {
        #region Properties
        public SqlDataReader data { get; set; }
        public DataTable dataTable { get; set; }
        private string connectionString { get; set; }
        private SqlConnection _connection { get; set; }
        private SqlCommand _cmd { get; set; }
        #endregion

        public DBHelper(string dataSource, string database) // Local
        {
            this.connectionString = @"Data Source=" + dataSource + "; Initial Catalog=" + database + "; Integrated Security=True";
        }
        /// <summary>
        /// Connect to external database
        /// </summary>
        /// <param name="dataSource">Source of database</param>
        /// <param name="username">Name of database</param>
        /// <param name="password">Password for database</param>
        public DBHelper(string dataSource, string username, string password) // External with usr and pwd
        {
            this.connectionString = @"Data Source=" + dataSource + ";Persist Security Info=True;User ID=" + username + ";Password=" + password;
        }

        #region Methods
        public bool Connect() // Connect to database
        {
            try
            {
                _connection = new SqlConnection(this.connectionString);
                _cmd = new SqlCommand();
                _cmd.Connection = _connection;
                return true;
            }
            catch (Exception e) { return false; }
        }
        public bool IsConnected() //Test connection - Tien
        {
            try
            {
                _connection.Open();
                _connection.Close();
                return true;
            }
            catch (Exception e) { return false; }
        }
        public void Open() // Open connection 
        { _connection.Open(); }
        public void Close() // Close connection
        {
            _connection.Close();
        }
        public bool InsertRow(string table, string fields, string values)// Insert new row
        {
            string sql = "INSERT INTO " + table + " (" + fields + ") VALUES (" + values + ")";
            _cmd.CommandText = sql; // Set SQL
            int affectedRows = _cmd.ExecuteNonQuery(); // Execute CMD
            return (affectedRows == 1) ? true : false; // Return true or false
        }
        public bool UpdateRow(string table, string fieldsAndValues, string where)// Update data
        {
            string sql = "UPDATE " + table + " SET " + fieldsAndValues + " WHERE " + where;
            _cmd.CommandText = sql; // Set SQL
            int affectedRows = _cmd.ExecuteNonQuery(); // Execute CMD
            return (affectedRows == 1) ? true : false; // Return true or false
        }
        public bool DeleteRow(string table, string where)// Delete row
        {
            string sql = "DELETE FROM " + table + " WHERE " + where;
            _cmd.CommandText = sql; // set sql
            int affectedRows = _cmd.ExecuteNonQuery(); // execute CMD
            return (affectedRows == 1) ? true : false; // Return true or false
        }
        public void SelectData(string fields, string table, string where, string orderBy)// Get data
        {
            string sql;
            string what = (fields == string.Empty) ? "*" : fields;
            sql = "SELECT " + what + " FROM " + table + " ";
            if (where != "")
                sql += "WHERE " + where + " ";
            if (orderBy != "")
                sql += "ORDER BY " + orderBy;
            _cmd.CommandText = sql; // Set SQL
            _cmd.CommandType = CommandType.Text; //set cmd type
            this.data = _cmd.ExecuteReader(); // execute CMD
        }
        public void SelectDataSP(string StoredProcedureName, params object[] pars)//Get data - Stored Procedure - Trang
        {
            _cmd.CommandText = StoredProcedureName; // Set SQL
            _cmd.Parameters.Clear();
            _cmd.CommandType = CommandType.StoredProcedure; // Set CMD type
            for (int i = 0; i < pars.Count(); i += 2)
            {
                SqlParameter par = new SqlParameter(pars[i].ToString(), pars[i + 1]);
                _cmd.Parameters.Add(par);
            }
            this.data = _cmd.ExecuteReader(); // execute CMD
        }
        #endregion
    }
}
